# _*_ coding:utf-8 _*_
# @File  : user_op.py
# @Time  : 2021-06-10 15:06
# @Author: zizle
import datetime

import pandas as pd
from fastapi import APIRouter, Depends, Body, Query, Path
from pydantic import BaseModel
from db import FAConnection
from interfaces.depends import admin_logged_require
from hutool import security
from status import r_status
from hutool.utils import create_random_string
from category import USER_ROLES, VARIETY_GROUPS, EXCHANGE, MENUS, CLIENTS

op_user_api = APIRouter()


# ############################# 用户 ######################################
#
# class UserItem(BaseModel):
#     username: str
#     phone: str = ''
#     email: str
#     password: str
#     role: str
#     note: str = ''
#     is_active: int = 1
#
#
# # uop = user-operation用户管理
# @op_user_api.post('/user/', summary='添加一个用户')
# async def create_user(person: dict = Depends(admin_logged_require), user_item: UserItem = Body(...)):
#     # 解密得到account和password
#     phone = security.rsa_decrypt(user_item.phone, key_tuple=0)
#     # 随意验证一下手机
#     if len(phone) != 11 or not phone.isdigit():
#         return {'code': r_status.VALIDATE_ERROR, 'message': '手机号格式错误!'}
#     password = security.rsa_decrypt(user_item.password, key_tuple=0)
#     password = password if password else phone[-6:]
#     hash_password = security.cipher_user_password(password)
#     user_code = 'Rui_' + create_random_string(10)
#     username = user_code if not user_item.username else user_item.username
#     sql = 'INSERT INTO user_user (user_code,username,phone,email,password_hashed,role,note) ' \
#           'VALUES (%s,%s,%s,%s,%s,%s,%s);'
#     db = FAConnection(conn_name='添加用户')
#
#     # 查询手机号是否存在,存在不能注册
#     is_exists = db.query(sql='SELECT id FROM user_user WHERE phone=%s LIMIT 1;', param=[phone], keep_conn=True)
#     if is_exists:
#         db.close()
#         return {'code': r_status.VALIDATE_ERROR, 'message': '手机号已存在,请直接登录.'}
#
#     count, _ = db.insert(sql, [user_code, username, phone, user_item.email, hash_password, user_item.role,
#                                user_item.note])
#     if count:
#         return {'code': r_status.CREATED_SUCCESS, 'message': '创建用户成功!'}
#     else:
#         return {'code': r_status.SERVER_ERROR, 'message': '创建用户失败,原因未知!'}
#

@op_user_api.get('/user/', summary='获取用户列表')
async def get_system_users(person: dict = Depends(admin_logged_require), page: int = Query(..., ge=1),
                           page_size: int = Query(..., ge=10, le=200)):
    record_start = (page - 1) * page_size
    record_offset = page_size
    sql = 'SELECT SQL_CALC_FOUND_ROWS id,join_time,recent_login,user_code,username,phone,email,role,expire,' \
          'note,is_active,admin_name ' \
          'FROM user_user ' \
          'ORDER BY recent_login DESC LIMIT %s,%s;'
    db = FAConnection()
    records = db.query(sql, param=[record_start, record_offset], keep_conn=True)
    total_obj = db.query('SELECT FOUND_ROWS() AS total;', fetchone=True)
    total_count = total_obj[0]['total'] if total_obj else 0  # 当前总数量
    today = datetime.datetime.today().strftime('%Y-%m-%d')
    for user_item in records:
        user_item['role_name'] = USER_ROLES.get(user_item['role'], '未知')
        user_item['join_time'] = user_item['join_time'].strftime('%Y-%m-%d %H:%M:%S')
        user_item['recent_login'] = user_item['recent_login'].strftime('%Y-%m-%d %H:%M:%S')
        user_item['expire'] = user_item['expire'].strftime('%Y-%m-%d') if user_item['expire'] else '2000-01-01'
        user_item['is_active'] = 1 if user_item['expire'] >= today else 0
    return {'code': r_status.SUCCESS, 'message': '查询成功!', 'users': records, 'page': page, 'page_size': page_size,
            'total_count': total_count}


# @op_user_api.get('/user/application/', summary='申请记录')
# async def get_application(person: dict = Depends(admin_logged_require),
#                           page: int = Query(..., ge=1), page_size: int = Query(..., ge=10, le=200)):
#     record_start = (page - 1) * page_size
#     record_offset = page_size
#     sql = 'SELECT SQL_CALC_FOUND_ROWS id,create_time,username,phone,client_code,is_passed ' \
#           'FROM user_application ' \
#           'ORDER BY create_time DESC LIMIT %s,%s;'
#     db = FAConnection()
#     records = db.query(sql, param=[record_start, record_offset], keep_conn=True)
#     total_obj = db.query('SELECT FOUND_ROWS() AS total;', fetchone=True)
#     total_count = total_obj[0]['total'] if total_obj else 0  # 当前总数量
#     for apply_item in records:
#         apply_item['create_time'] = apply_item['create_time'].strftime('%Y-%m-%d %H:%M:%S')
#
#     return {'code': r_status.SUCCESS, 'message': '查询成功!', 'applications': records, 'page': page, 'page_size': page_size,
#             'total_count': total_count}

# @op_user_api.put('/user/application/{apply_id}', summary='修改申请记录状态')
# async def update_application_row(person: dict = Depends(admin_logged_require), apply_id: int = Path(..., ge=1)):
#     # 修改通过状态
#     sql = 'UPDATE user_application SET is_passed=1 WHERE id=%s;'
#     db = FAConnection(conn_name='修改申请记录')
#     _, success = db.execute(sql, [apply_id])
#     if success:
#         return {'code': r_status.SUCCESS, 'message': '申请记录已同意!'}
#     return {'code': r_status.UNKNOWN_ERROR, 'message': '申请记录同意失败!'}
#

@op_user_api.put('/user/{op_id}/', summary='修改一个用户的信息')
async def put_system_user(person: dict = Depends(admin_logged_require), user_item: dict = Body(...),
                          op_id: int = Path(..., ge=1)):
    # 过滤一下数据
    columns = ['username', 'phone', 'password', 'email', 'role', 'expire', 'note', 'clients', 'admin_name']
    exclude_keys = []
    for k in user_item.keys():
        if k not in columns:
            exclude_keys.append(k)
    for k in exclude_keys:
        del user_item[k]

    # 根据前端上传的key进行修改

    if 'phone' in user_item.keys():  # 随意验证一下手机
        phone = user_item['phone']
        if len(phone) != 11 or not phone.isdigit():
            return {'code': r_status.VALIDATE_ERROR, 'message': '手机号格式错误!'}
    if 'password' in user_item.keys():  # 设置密码
        user_item['password_hashed'] = security.cipher_user_password(user_item['password'])
        del user_item['password']

    # 组织sql语句
    sql_columns = ' '
    for k, v in user_item.items():
        sql_columns += f'{k}=%({k})s,'
    sql_columns = sql_columns[:-1] + ' '
    user_item['id'] = op_id
    sql = 'UPDATE user_user SET' + sql_columns + 'WHERE id=%(id)s LIMIT 1;'
    db = FAConnection(conn_name='ModifyUser')
    _, success = db.execute(sql, param=user_item)
    if success:
        return {'code': r_status.SUCCESS, 'message': '修改成功!'}
    else:
        return {'code': r_status.SUCCESS, 'message': '修改失败,原因未知!'}


# ############################# 用户客户端权限 ######################################


@op_user_api.get('/user/client/', summary='获取客户端信息及用户可登录')
async def get_client_user(u: int = Query(..., ge=1)):
    # 查询当前用户当前绑定的客户端情况
    sql = 'SELECT id,clients,role FROM user_user WHERE id=%s LIMIT 1;'
    db = FAConnection(conn_name='查询绑定客户端')
    records = db.query(sql, param=[u], fetchone=True, keep_conn=True)
    user_obj = records[0]
    if user_obj['role'] in ['superuser', 'operator', 'collector', 'research']:
        db.close()
        return {'code': r_status.SUCCESS, 'message': '内部人员,不限制登录客户端!', 'clients': [], 'is_all': True}
    if not user_obj['clients']:
        db.close()
        return {'code': r_status.SUCCESS, 'message': '还没绑定任何客户端!', 'clients': [], 'is_all': False}
    # 查询客户端名称
    bind_clients = tuple(user_obj['clients'].split(';'))
    client_sql = 'SELECT client_name,client_code,category FROM sys_client WHERE client_code in %s;'
    bind_client_rows = db.query(client_sql, param=[bind_clients])
    for c in bind_client_rows:
        c['category_name'] = CLIENTS.get(c['category'])
    return {'code': r_status.SUCCESS, 'message': '查询客户端成功!', 'data': bind_client_rows}



# ############################# 用户功能权限 ######################################


@op_user_api.get('/user.menu/', summary='用户菜单权限查询')
async def get_user_menu_auth(u: int = Query(..., ge=1)):
    sql = 'SELECT smtb.id,smtb.parent_id,smtb.category,smtb.name_en,smtb.name_zh,b.expire ' \
          'FROM sys_menu AS smtb ' \
          'LEFT JOIN user_user_reject_menu AS b ' \
          'ON smtb.id=b.menu_id AND b.user_id=%s ' \
          'ORDER BY smtb.create_time,smtb.category;'
    db = FAConnection(conn_name='查询用户功能权限')
    records = db.query(sql, [u])
    df = pd.DataFrame(records)
    now_date = datetime.datetime.now().strftime('%Y-%m-%d')
    df['expire'] = df['expire'].apply(lambda x: '' if x is None else x.strftime('%Y-%m-%d'))
    df['auth'] = df['expire'].apply(lambda x: 0 if x and x >= now_date else 1)
    df['category_name'] = df['category'].apply(lambda x: MENUS.get(x, '未知'))
    # 取parent_id=0
    parent_df = df[df['parent_id'] == 0]
    rep_data = []
    for row in range(parent_df.shape[0]):
        row_df = parent_df[row: row + 1]
        menu_obj = row_df.to_dict(orient='records')[0]
        child_df = df[df['parent_id'] == menu_obj['id']].copy()
        # if not menu_obj['auth']:
        #     child_df['auth'] = [0 for _ in range(child_df.shape[0])]
        menu_obj['children'] = child_df.to_dict(orient='records')
        menu_obj['_showChildren'] = 1  # 前端树形图表格默认展开的标志
        rep_data.append(menu_obj)
    return {'code': r_status.SUCCESS, 'message': '查询菜单权限成功!', 'data': rep_data}


class UserMenuAuth(BaseModel):
    user_id: int
    expire: str


@op_user_api.post('/user.menu/{menu_id}/', summary='新建一个用户功能权限记录')
async def create_user_menu(person: dict = Depends(admin_logged_require), menu_id: int = Path(..., ge=1),
                           auth_item: UserMenuAuth = Body(...)):
    try:
        expire_date = datetime.datetime.strptime(auth_item.expire, '%Y-%m-%d')
    except ValueError:
        return {'code': r_status.VALIDATE_ERROR, 'message': '创建失败,expire格式错误!'}
    db = FAConnection(conn_name='创建菜单权限')
    # 查询该菜单的子菜单
    query_children = 'SELECT id FROM sys_menu WHERE parent_id=%s;'
    children_menus = db.query(query_children, [menu_id], keep_conn=True)
    reject_objects = [{'creator': person['uid'], 'user_id': auth_item.user_id, 'menu_id': menu_id}]
    reject_children = [{'creator': person['uid'], 'user_id': auth_item.user_id, 'menu_id': c['id']} for c in
                       children_menus]
    rejects = reject_objects + reject_children

    # 添加限制日期
    for r in rejects:
        r['expire'] = expire_date.strftime('%Y-%m-%d')

    # 加入数据库
    insert_sql = 'INSERT IGNORE INTO user_user_reject_menu (creator,user_id,menu_id,expire) ' \
                 'VALUES (%(creator)s,%(user_id)s,%(menu_id)s,%(expire)s);'
    count, _ = db.insert(insert_sql, param=rejects, many=True, keep_conn=True)
    # 恢复auto_increment(纯属强迫症所为)
    db.correct_table_auto_increment(table_name='user_user_reject_menu', finish_closed=True)
    return {'code': r_status.SUCCESS, 'message': '设置成功!'}


@op_user_api.put('/user.menu/{menu_id}/', summary='修改一个用户功能权限记录')
async def put_user_menu(person: dict = Depends(admin_logged_require), menu_id: int = Path(..., ge=1),
                        auth_item: UserMenuAuth = Body(...)):
    try:
        expire_date = datetime.datetime.strptime(auth_item.expire, '%Y-%m-%d')
    except ValueError:
        return {'code': r_status.VALIDATE_ERROR, 'message': '创建失败,expire格式错误!'}
    # 查询子菜单，并设置所有子菜单
    db = FAConnection(conn_name='修改菜单权限')
    # 查询该菜单的子菜单
    query_children = 'SELECT id FROM sys_menu WHERE parent_id=%s;'
    children_menus = db.query(query_children, [menu_id], keep_conn=True)
    reject_objects = [{'creator': person['uid'], 'user_id': auth_item.user_id, 'menu_id': menu_id}]
    reject_children = [{'creator': person['uid'], 'user_id': auth_item.user_id, 'menu_id': c['id']} for c in
                       children_menus]
    rejects = reject_objects + reject_children

    # 添加限制日期
    for r in rejects:
        r['expire'] = expire_date.strftime('%Y-%m-%d')

    sql = 'UPDATE user_user_reject_menu SET creator=%(creator)s,expire=%(expire)s ' \
          'WHERE user_id=%(user_id)s AND menu_id=%(menu_id)s;'
    success = db.execute(sql, param=rejects, many=True)
    if success:
        return {'code': r_status.SUCCESS, 'message': '修改成功!'}
    else:
        return {'code': r_status.UNKNOWN_ERROR, 'message': '修改失败,原因未知!'}


# ############################# 用户品种权限 ######################################


@op_user_api.get('/user.variety/', summary='用户品种权限查询')
async def get_variety_user(u: int = Query(..., ge=1)):
    sql = 'SELECT vtb.id,vtb.category,vtb.exchange,vtb.variety_name,vtb.variety_en,spv.expire_date ' \
          'FROM sys_variety AS vtb ' \
          'LEFT JOIN user_user_variety AS spv ' \
          'ON vtb.variety_en=spv.variety_en AND spv.user_id=%s;'
    db = FAConnection(conn_name='query user.variety')
    records = db.query(sql, [u], keep_conn=True)
    # 查询用户身份
    user_obj = db.query('SELECT id,role FROM user_user WHERE id=%s;', param=[u], fetchone=True)
    user_role = user_obj[0]['role'] if len(user_obj) > 0 else ''
    now_str = datetime.datetime.now().strftime('%Y-%m-%d')
    for item in records:
        item['category_name'] = VARIETY_GROUPS.get(item['category'], '未知')
        item['exchange_name'] = EXCHANGE.get(item['exchange'], '未知')
        item['expire_date'] = item['expire_date'] if item['expire_date'] else ''
        item['is_auth'] = 0
        if item['expire_date'] and item['expire_date'] >= now_str:
            item['is_auth'] = 1
        if user_role in ['superuser', 'operator']:
            item['is_auth'] = 1
            item['expire_date'] = '9999-99-99'
    return {'code': r_status.SUCCESS, 'message': '查询用户品种权限成功!', 'data': records}


class UserVarietyAuth(BaseModel):
    user_id: int
    variety_en: str
    expire_date: str


@op_user_api.post('/user.variety/', summary='添加用户品种权限')
async def create_user_menu(person: dict = Depends(admin_logged_require), auth_item: UserVarietyAuth = Body(...)):
    sql = 'INSERT IGNORE INTO user_user_variety (user_id,variety_en,expire_date) VALUES (%s,%s,%s);'
    db = FAConnection(conn_name='AddUserVariety')
    count, _ = db.insert(sql, [auth_item.user_id, auth_item.variety_en, auth_item.expire_date], keep_conn=True)
    # 强迫症设置auto_increment
    db.correct_table_auto_increment(table_name='user_user_variety', finish_closed=True)
    if count:
        return {'code': r_status.CREATED_SUCCESS, 'message': '创建成功!'}
    else:
        return {'code': r_status.UNKNOWN_ERROR, 'message': '创建失败,原因未知!'}


@op_user_api.put('/user.variety/', summary='修改一个用户品种权限记录')
async def put_user_menu(person: dict = Depends(admin_logged_require), auth_item: UserVarietyAuth = Body(...)):
    sql = 'UPDATE user_user_variety SET expire_date=%s WHERE user_id=%s AND variety_en=%s;'
    db = FAConnection(conn_name='Modify user.variety')
    _, success = db.execute(sql, [auth_item.expire_date, auth_item.user_id, auth_item.variety_en])
    if success:
        return {'code': r_status.SUCCESS, 'message': '修改成功!'}
    else:
        return {'code': r_status.UNKNOWN_ERROR, 'message': '修改失败,原因未知!'}
